import os

import openpyxl

from pratice.pratice1 import DButils


class rExcel:
    @staticmethod
    def read_excel(workbook, sheet_name,row_start=2,row_end=None,col_start=1,col_end=None):
        workbook = openpyxl.load_workbook(workbook)
        sheet = workbook[sheet_name]
        if row_end == None:
            row_end = sheet.max_row
        if col_end == None:
            col_end = sheet.max_column
        if row_start > row_end or col_start > col_end:
            return []
        return [[sheet.cell(row=i, column=j).value for j in range(col_start,col_end+1)] for i in range(row_start,row_end+1)]

    @staticmethod
    def write_excel(workbook, sheet_name, data, row_start=2, col_start=1):
        workbook = openpyxl.load_workbook(workbook)
        sheet = workbook[sheet_name]
        for i in range(len(data)):
            for j in range(len(data[i])):
                sheet.cell(row=i+row_start, column=j+col_start).value = data[i][j]

'''
CREATE TABLE `user_info` (
  `id` int(11) ,
  `idcard` varchar(50) ,
  `username` varchar(50) ,
  `realname` varchar(50) ,
  `pwd` varchar(50) ,
  `telphone` varchar(12) ,
  `email` varchar(100) ,
  `age` int(11) ,
  `sex` varchar(20) ,
  `address` varchar(200) ,
  `hiredate` date ,
  `sal` double(9,2) ,
  `job` varchar(100) ,
  `company` varchar(100) 
) ;
'''

# for j in range(0, 15):
#     rExcel.read_excel('renyuan.xlsx', '人员管理', 2 + i, j + 1)
#     sql = '''
#            insert into user_info(id,idcard,username,realname,pwd,telphone,email,age,sex,address,hiredate,sal,job,company)
#            values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
#            '''

def main():
    # 创建一个excel文件
    # workbook =openpyxl.Workbook("test.xlsx")
    # workbook.create_sheet("one of user")
    # workbook.save("test.xlsx")
    # print(os.path.exists("test.xlsx"))
    # print(rExcel.read_excel("test.xlsx", "Sheet"))
    # print(os.path.exists("renyuan.xlsx"))
    # data = rExcel.read_excel("renyuan.xlsx","Sheet1",2,2001,1,14)
    db=DButils.DButils()
    data = rExcel.read_excel("renyuan.xlsx", "Sheet1", 2, 2001, 1, 14)

    # 构建基本的SQL插入语句
    sql = '''insert into user_info(`id`,`idcard`,`username`,`realname`,`pwd`,`telphone`,`email`,`age`,`sex`,`address`,`hiredate`,`sal`,`job`,`company`) values '''

    # 为每行数据添加一个插入语句
    values_strings = []
    for row in data:
        values_strings.append("(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)")
    # 将二维数组扁平化，因为 db.update() 需要一个一维数组作为参数
    flat_data = [item for sublist in data for item in sublist]
    # 将所有的插入语句合并为一个SQL语句
    sql += ','.join(values_strings)

    db.select("select * from user_info")




main()